import datetime
from typing import List, Dict

from sqlalchemy import Column, DateTime, Date, BIGINT, DECIMAL, String, Index, and_, func
from sqlalchemy.orm import Session

from common.database import Base
from common.database import SessionMixin


class BdlDiskCapacityInfo(Base, SessionMixin):
    __tablename__ = 'bdl_disk_capacity_info'
    ident = Column(String(128), nullable=False, comment="服务器名称")
    path = Column(String(128), nullable=False, comment="挂在路径")
    disk_total = Column(DECIMAL(16, 2), nullable=False, comment="磁盘原始容量")
    disk_free = Column(DECIMAL(16, 2), nullable=False, comment="磁盘剩余容量")
    usage_rate = Column(DECIMAL(16, 2), nullable=False, comment="磁盘使用率")
    create_date = Column(Date, default=datetime.date.today, nullable=True, comment="创建日期")
    create_time = Column(DateTime, default=datetime.datetime.now, nullable=False, comment="创建时间")

    __table_args__ = (
        Index('idx_bdl_disk_capacity_info_uuid', 'uuid', unique=True),
        Index('idx_bdl_disk_capacity_info_ident', 'ident'),
        Index('idx_bdl_disk_capacity_info_create_date', 'create_date'),
        Index('idx_bdl_disk_capacity_info_create_time', 'create_time'),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic',
        }
    )

    def __repr__(self):
        return "<ident='{}'>".format(self.ident)

    # select create_date, sum(disk_total) as disk_total ,sum(disk_free) as disk_free ,round((1 - (disk_free / disk_total *100)) * 100  ,2)  as usage_rate from bdl_disk_capacity_info where path!='/disk1' and create_date between "2024-01-30" and "2024-02-01"  group by create_date;
    @classmethod
    def get_hdfs_total_capacity(cls, db: Session, start_date: str, end_date: str) -> List:
        return db.query(cls).with_entities(cls.create_date, func.sum(cls.disk_total).label('磁盘总体容量单位(TB)'),
                                           func.sum(cls.disk_free).label('磁盘剩余容量单位(TB)'),
                                           func.round((1 - func.sum(cls.disk_free) / func.sum(cls.disk_total)) * 100, 2).label(
                                               '磁盘使用率单位(%)')).filter(
            and_(cls.create_date.between(start_date, end_date), cls.path == '/disk1')).group_by(cls.create_date).order_by(
            cls.create_date.desc()).to_dict()

    # select create_date, sum(disk_total) as disk_total ,sum(disk_free) as disk_free ,round(1 - disk_free / disk_total ,2)  as usage_rate from bdl_disk_capacity_info where path!='/disk1' create_date between "2024-01-30" and "2024-02-01"  group by create_date;
    @classmethod
    def get_kudu_total_capacity(cls, db: Session, start_date: str, end_date: str) -> List:
        return db.query(cls).with_entities(cls.create_date, func.sum(cls.disk_total).label('磁盘总体容量单位(TB)'),
                                           func.sum(cls.disk_free).label('磁盘剩余容量单位(TB)'),
                                           func.round((1 - func.sum(cls.disk_free) / func.sum(cls.disk_total)) * 100, 2).label(
                                               '磁盘使用率单位(%)')).filter(
            and_(cls.create_date.between(start_date, end_date), cls.path != '/disk1')).group_by(cls.create_date).order_by(
            cls.create_date.desc()).to_dict()


if __name__ == '__main__':
    from common.database import SessionLocal

    session = SessionLocal()

    print(BdlDiskCapacityInfo.get_hdfs_total_capacity(session, "2024-01-30", "2024-02-01"))
    print(BdlDiskCapacityInfo.get_kudu_total_capacity(session, "2024-01-30", "2024-02-01"))
    session.close()
